Towards a general purpose, multidimensional index: integration, optimization, and enhancement of UB-trees

Multidimensional access methods are considered to be a promising approach for providing acceptable performance to analysis-centric applications. However, despite the large body of research work in this field, the commercial support for multidimensional indexes is still very weak. The reason for this discrepancy is threefold: first, no standard multidimensional index like the B-Tree for one-dimensional data has emerged so far. Second, integrating a new access method into a database system kernel is usually a complex and expensive task. Third, current query optimizers still have problems in dealing with multidimensional data making it difficult to use multidimensional indexes efficiently. In this thesis, we address the above mentioned deficiencies and promote the universal B-Tree (UB-Tree) as a premier candidate for a general-purpose, multidimensional index. In an extensive theoretical and experimental comparison with R*-Trees we show that the UB-Tree can compete with other approaches in multidimensional indexing. The UB-Tree outperforms the R*-Tree not only w.r. to query performance but also considering the important properties of maintenance performance, index size and others. Addressing the kernel integration, we reveal another big advantage of UB-Trees: relying on the standard B-Tree as underlying structure the integration effort is reduced significantly. We further present optimizations of the basic algorithms, like the reduction of post-filtering, for range query processing, which have large impact in practice. To improve the support of query optimizers for multidimensional access methods, we propose a new type of multidimensional histograms based on UB-Tree concepts. At the same time, we point out general limitations of multidimensional synopses. Finally, we introduce the concept of weighted dimensions, which allows for tuning of the UB-Tree to application specific preferences among the dimensions. This makes the UB-Tree viable to a broader range of applications. The concept also leads to an improved range query processing for standard composite key indexes.